<?php
class Need_model extends Model {

    
    function insert_new($telefono)
    {
        $esc_estado_inicial = "'LLAMAR'";
        $esc_telefono = $this->db->escape($telefono);
        $sql = "INSERT INTO need (telefono, estado, timestamp) VALUES ($esc_telefono, $esc_estado_inicial, now());";
        $this->db->query($sql);
    }

    function re_insert($telefono)
    {
        $esc_estado_inicial = "'LLAMAR'";
        $esc_telefono = $this->db->escape($telefono);
        $sql = "UPDATE need SET cuenta = cuenta + 1 WHERE telefono=$esc_telefono AND estado = $esc_estado_inicial";
        $this->db->query($sql);
    }

    function exists($telefono)
    {
        $esc_estado_inicial = "'LLAMAR'";
        $esc_telefono = $this->db->escape($telefono);
        $sql = "SELECT id FROM need WHERE telefono=$esc_telefono AND estado = $esc_estado_inicial";
        $query = $this->db->query($sql);
        return ($query->num_rows() > 0);
    }


    function update($id, $estado="", $necesidad, $observacion)
    {
        $esc_id = $this->db->escape($id);
        $esc_estado = $this->db->escape($estado);
        $esc_necesidad = $this->db->escape($necesidad);
        $esc_observacion = $this->db->escape($observacion);
        $sql = "UPDATE need SET ";
        $sql .= "necesidad=$esc_necesidad ";
        if ($estado != "") $sql .= ", estado = $esc_estado ";
        if ($observacion !="") $sql .=" , observacion = CASE observacion WHEN '' THEN $esc_observacion ELSE CONCAT_WS('<br>', observacion, $esc_observacion) END ";
        $sql .= " WHERE id = $esc_id";
        $this->db->query($sql);
    }

    function get_where($status = "", $need = "", $from_time="", $limit = 1000)
    {
        $esc_status = $this->db->escape($status);
        $esc_need = $this->db->escape($need);
        $esc_from_time = $this->db->escape($from_time);
        $sql= "SELECT id, UNIX_TIMESTAMP(timestamp) as timestamp, telefono, estado, necesidad, observacion, cuenta FROM need WHERE 1 ";
        if ($status!="") $sql .= " AND estado=$esc_status";
        if ($need!="") $sql .= " AND necesidad=$esc_need";
        if ($from_time!="") $sql .= " AND timestamp > $esc_from_time";
        $sql .= " ORDER BY timestamp ASC limit $limit";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    function count_state($status = "RESUELTO")
    {
        $esc_status = $this->db->escape($status);
        $sql = "SELECT count(id) as n FROM need WHERE estado = $esc_status";
        $query = $this->db->query($sql);
        return $query->row('n');
    }

    function count_after($id)
    {
        $esc_id = $this->db->escape($id);
        $sql = "SELECT count(id) as n FROM need WHERE id > $id;";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->row('n');
    }
    
    function last_id()
    {
        $sql = "SELECT max(id) as n FROM need;";
        $query = $this->db->query($sql);
        return $query->row('n');
    }

    function get_time()
    {
        $sql = "SELECT now() as time;";
        $query = $this->db->query($sql);
        return $query->row('time');
    }

    function refresh_states()
    {
        $sql = "UPDATE need SET estado = 'LLAMAR' WHERE estado = 'RESOLVIENDO' AND (now() - timestamp) > 500;";
        $query = $this->db->query($sql);
        $sql = "DELETE FROM helping WHERE id IN (SELECT id FROM need WHERE estado<>'RESOLVIENDO')";
        $query = $this->db->query($sql);
    }

}

?>
